# -*- coding: utf-8 -*-
'''
@author: olivia.dou
Created on: 2018/11/12 20:55
desc: 和新闻模块相关的函数
'''

import random,logging,time,os
import pandas as pd
from util.db_util import execute_query
import base.globalvars as glo


#表名,字段名
table_dict = {"event":('nlp_news_event','name'),"concept":('nlp_news_concept','concept_name'),"industry":('nlp_news_industry','name'),
              "people":{'nlp_news_people','name'},"company":{"nlp_news_company":"abbr"},
              "product":('nlp_news_product','name'),"region":{"nlp_news_region":"region"}}

tag_table_info = pd.read_excel(os.path.split(os.path.abspath(__file__))[0] + "/tag_tables.xlsx")


def get_news_tags(tag_type, news_id = None):
    """获取新闻的标签
    #tag_type:标签类型 1:event, 2: industry, 3：concept, 7 product
    tag_type:标签类型 event(1), industry(2), concept(3), people(4),company(6), product(7),region(9)
    """
    field_name=tag_table_info[tag_table_info['tag_name']==tag_type]['field_name'].iloc[0]
    table_name=tag_table_info[tag_table_info['tag_name'] == tag_type]['table_name'].iloc[0]

    if news_id is not None:
        sql = "SELECT DISTINCT %s from %s t1 INNER JOIN nlp_news_feed news ON t1.oid = news.id WHERE news.id=%s" \
          % (field_name,table_name, news_id)
    else:
        sql = "SELECT DISTINCT %s FROM %s"%(field_name, table_name)
    resultset = execute_query(sql)
    if resultset != []:
        tags = [result[0] for result in resultset] #resultset的每个元素（元组）只有一个元素
        return tags
    else:
        return []

def news_exist(news_id, isStockReview="", filter_retain = 0, tag_dict={}):
    """检查新闻是否在数据库里存在
        news_id: 新闻id
        filter_retain: 过滤还是保留 0:不考虑标签作用，1：保留， -1：过滤
        tag_dict: 需要检查的标签字典形如{1:["上涨",xxx],2:[xxx,xxx]}
    """

    sql = "select * from nlp_news_summary news where oid = %s" % news_id
    if isStockReview!="":
        stock_review_part = " INNER JOIN nlp_news_additional a ON news.oid = a.oid"

    if filter_retain != 0:
        subquery = [" INNER JOIN %s ON news.oid = %s.oid where news.oid = '%s' and %s.%s in %s"
                    %(table_dict[tag_type][0],table_dict[tag_type][0],news_id,table_dict[tag_type][0],table_dict[tag_type][1],repr(tuple(tag_dict[tag_type])).replace(',)',')')) for tag_type in tag_dict.keys()]
        if filter_retain == 1:
            sql = "SELECT * FROM nlp_news_summary news " + ''.join(subquery)
        else: # filter_include==-1
            sql = "SELECT * FROM `nlp_news_summary` news where news.oid = %s and news.oid not in " \
                    "(SELECT news.oid FROM `nlp_news_summary` news"%news_id + ''.join(subquery) + ')'

    resultset = execute_query(sql)
    if resultset != ():
        return True
    else:
        return False

def get_random_newsid(userid, saved = False, isTitleNull=False, returnStr = True):
    """获取随机的新闻oid
    userid: 用户id
    saved: 是否已收藏。True为已收藏，False为未收藏
    isTitleNull：新闻的title字段是否为null"""

    favorite = "select oid from nlp_news_favorite where user_id=%d"%int(userid)

    if isTitleNull:
        sub = " AND title IS NULL"
    else:
        sub = " AND title IS NOT NULL"

    if saved:
        query = favorite + sub
    else:
        query = "select oid from nlp_news_summary where oid not in (%s)"%favorite + sub

    resultset = execute_query(query)
    rand_idx = random.randint(0, len(resultset) - 1)
    logging.debug(resultset[rand_idx][0])
    if returnStr:
        return str(resultset[rand_idx][0])
    else:
        return resultset[rand_idx][0]

def validate_news_result(post_data, res_json):
    """验证新闻页面结果"""
    #res_json = response.json()

    previous_time = time.time()
    # 验证有summary字段：算法正常工作
    for item in res_json['message']['content']:
        item_tim = time.strptime(item['tim'], '%Y-%m-%d %H:%M:%S')
        assert item['summary'] is not None,"没有生成summary, 请检查算法是否正常工作: %s"%item['title']

        if post_data['params[sort]'] == 'tim':
            assert time.mktime(item_tim) <= previous_time, '新闻时间排序不正确: %s' % item['title']  # 验证新闻按时间降序排列
        previous_time = time.mktime(item_tim)

    return True

def get_latest_oid():
    """若全局变量latest_oid未设置，则返回最新oid，同时设置全局变量latest_oid，否则返回全局变量latest_oid的值"""
    #Todo: 根据uid,type查询未收藏的新闻

    oid = glo.get_value("latest_oid")
    if oid is None:
        sql = """SELECT * FROM `nlp_news_feed` where white=1 order by tim desc limit 1;"""
        oid = execute_query(sql)[0][0]
        glo.set_value("latest_oid",oid)
    return oid




